package jdbctemplate;

import domain.Emp;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class JdbcTemplateDemo2 {
    //Junit单元测试，可以让方法独立执行
    //1.获取JDBCTemplate对象
  private  JdbcTemplate jt = new JdbcTemplate(JDBCUtils.getDataSource());
    /**
     * 一、修改user的数据score为100
     */
    @Test
    public void test1(){
        //2.定义sql 改
        String sql = "update  user set pwd=777 where id=5";
        //3.执行sql
        int count = jt.update(sql);
        System.out.println(count);
    }
    /**
     * 二、添加一条记录
     */
    @Test
    public void test2(){
        //2.定义sql 增
        String sql = "insert into user(id,name,pwd,score) values(?,?,?,?)";
        //3.执行sql
        int count = jt.update(sql,5,"王八",001,78);
        System.out.println(count);
    }
    /**
     * 三、删除刚才添加的记录
     */
    @Test
    public void test3(){
        //2.定义sql 删
        String sql = "delete from user where id=?";
        //3.执行sql
        int count = jt.update(sql,5);
        System.out.println(count);
    }
    /**
     * 四、查询id=1的记录，将其封装为Map集合
     * 注意：这个方法查询的结果集长度只能是1
     */
    @Test
    public void test4(){
        //定义sql 查
        String sql = "select * from user where id=?";
        //3.执行sql
        Map<String, Object> map= jt.queryForMap(sql,1);
        System.out.println(map);
    }
    /**
     * 五、查询所有记录，将其封装成List
     */
    @Test
    public void test5(){
        //定义sql
      String sql = "select * from user";
        //3.执行sql
      List<Map<String, Object>> list = jt.queryForList(sql);
      for(Map<String, Object> map : list){
          System.out.println(map);
      }
    }
    /**
     * 六、查询所有记录，将其封装为Emp对象的List集合
     */
    @Test
    public void test6_1(){
        //定义sql
        String sql = "select * from user";
        //执行sql
              List<Emp>list =jt.query(sql, new RowMapper<Emp>() {
            @Override
            public Emp mapRow(ResultSet rs, int i) throws SQLException {
                Emp emp = new Emp();
               int id = rs.getInt("id");
               String name = rs.getString("name");
               String pwd = rs.getString("pwd");
               Double score = rs.getDouble("score");
               emp.setId(id);
               emp.setName(name);
               emp.setPwd(pwd);
               emp.setScore(score);
                return emp;
            }
        });
         for(Emp emp : list){
             System.out.println(emp);
         }
        }
    /**
     * 六、查询所有记录，将其封装为Emp对象的List集合
     */
    @Test
    public void test6_2(){
        //定义sql
        String sql = "select * from student";
        //执行sql
       List<Emp> list = jt.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
       for(Emp emp : list){
           System.out.println(emp);
         }
       }
    /**
     * 七、查询总记录数
     */
    @Test
    public void test7(){
        //定义sql
        String sql = "select count(id) from user";
         Long total=jt.queryForObject(sql,Long.class);
        System.out.println(total);

    }
     }


